﻿using Logistics.DataAccess;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OracleClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Logistics.DataAccess
{
    public class tc_Permission
    {
        public bool Add(Model.tc_Permission model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("INSERT INTO TC_PERMISSIONS(");
            strSql.Append("ID,NAME,ISGRANTED,TYPE,USERID,ROLEID)");
            strSql.Append(" VALUES (");
            strSql.Append("@in_ID,@in_NAME,@in_ISGRANTED,@in_TYPE,@in_USERID,@in_ROLEID)");
            OracleParameter[] cmdParms = {
                DBHelper.CreateDbParameter("@in_ID", DbType.String, model.Id.ToString().Length ,model.Id.ToString()),
                DBHelper.CreateDbParameter("@in_NAME", DbType.String,50, DBValue.GetDBValue(model.Name)),
                DBHelper.CreateDbParameter("@in_ISGRANTED", DbType.Int32,0, model.IsGranted),
                DBHelper.CreateDbParameter("@in_TYPE", DbType.Int32,0, model.Type.GetHashCode()),
                DBHelper.CreateDbParameter("@in_USERID", DbType.String,36, model.UserId.ToString()),
                DBHelper.CreateDbParameter("@in_ROLEID", DbType.String,36, model.RoleId.ToString())};

            return DBHelper.ExecuteSql(strSql.ToString(), cmdParms) > 0;
        }

        public bool Update(Model.tc_Permission model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("UPDATE TC_PERMISSIONS SET ");
            strSql.Append("NAME=@in_NAME,");
            strSql.Append("ISGRANTED=@in_ISGRANTED,");
            strSql.Append("TYPE=@in_TYPE,");
            strSql.Append("USERID=@in_USERID,");
            strSql.Append("ROLEID=@in_ROLEID");
            strSql.Append(" WHERE ID=@in_ID");
            OracleParameter[] cmdParms = {
               DBHelper.CreateDbParameter("@in_NAME", DbType.String,50, DBValue.GetDBValue(model.Name)),
                DBHelper.CreateDbParameter("@in_ISGRANTED", DbType.Int32,0, model.IsGranted),
                DBHelper.CreateDbParameter("@in_TYPE", DbType.Int32,0, model.Type.GetHashCode()),
                DBHelper.CreateDbParameter("@in_USERID", DbType.String,36, model.UserId.ToString()),
                DBHelper.CreateDbParameter("@in_ROLEID", DbType.String,36, model.RoleId.ToString()),
                DBHelper.CreateDbParameter("@in_ID", DbType.String, model.Id.ToString().Length ,model.Id.ToString())};

            return DBHelper.ExecuteSql(strSql.ToString(), cmdParms) > 0;
        }

        public Model.tc_Permission GetModel(Guid id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM TC_PERMISSIONS ");
            strSql.Append(" WHERE ID=@in_ID");
            OracleParameter[] cmdParms = {
                DBHelper.CreateDbParameter("@in_ID", DbType.String, id.ToString().Length ,id.ToString())};

            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString(), cmdParms))
            {
                if (dr.Read())
                {
                    return GetModel(dr);
                }
                return null;
            }
        }
        public Model.tc_Permission GetModel(string userId, string name)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM TC_PERMISSIONS ");
            strSql.Append(" WHERE NAME=@in_NAME  AND USERID=@in_USERID");
            OracleParameter[] cmdParms = {
                DBHelper.CreateDbParameter("@in_USERID", DbType.String,36, userId.ToString()),
                DBHelper.CreateDbParameter("@in_NAME", DbType.String,50 ,name)};

            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString(), cmdParms))
            {
                if (dr.Read())
                {
                    return GetModel(dr);
                }
                return null;
            }
        }
        public bool Delete(Guid id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("DELETE FROM TC_PERMISSIONS ");
            strSql.Append(" WHERE ID=@in_ID");
            OracleParameter[] cmdParms = {
                DBHelper.CreateDbParameter("@in_ID", DbType.String,  id.ToString().Length,id.ToString())};

            return DBHelper.ExecuteSql(strSql.ToString(), cmdParms) > 0;
        }


        public List<Model.tc_Permission> GetList(Guid userId, Model.EPermissionType type)
        {
            StringBuilder strSql = new StringBuilder("SELECT * FROM TC_PERMISSIONS");
            strSql.Append(" WHERE USERID=@in_USERID AND TYPE=@in_TYPE");
            OracleParameter[] cmdParms = {
                DBHelper.CreateDbParameter("@in_USERID", DbType.String,  userId.ToString().Length,userId.ToString()),
                DBHelper.CreateDbParameter("@in_TYPE", DbType.Int32, 0,type.GetHashCode())};
            using (DbDataReader dr = DBHelper.ExecuteReader(strSql.ToString(), cmdParms))
            {
                List<Model.tc_Permission> lst = GetList(dr);
                return lst;
            }
        }



        #region 
        /// <summary>
        /// 由一行数据得到一个实体
        /// </summary>
        private Model.tc_Permission GetModel(DbDataReader dr)
        {
            Model.tc_Permission model = new Model.tc_Permission();
            model.Id = DBHelper.GetGuid(dr["ID"]);
            model.Name = DBHelper.GetString(dr["NAME"]);
            model.IsGranted = DBHelper.GetBool(dr["ISGRANTED"]);
            model.Type = (Model.EPermissionType)DBHelper.GetInt(dr["TYPE"]);
            model.UserId = DBHelper.GetGuid(dr["USERID"]);
            model.RoleId = DBHelper.GetGuid(dr["ROLEID"]);
            return model;
        }

        /// <summary>
        /// 由DbDataReader得到泛型数据列表
        /// </summary>
        private List<Model.tc_Permission> GetList(DbDataReader dr)
        {
            List<Model.tc_Permission> lst = new List<Model.tc_Permission>();
            while (dr.Read())
            {
                lst.Add(GetModel(dr));
            }
            return lst;
        }
        #endregion
    }
}
